
import random
from tkinter import *
from tkinter.ttk import *
from tkinter import filedialog, ttk
import pandas as pd
import tkinter as tk
from ExceLRead_DBC import file_Path
from cantools.database.can import Message,Signal,Database,Node
import cantools

class WinGUI(Tk):
    excelpath=0
    def __init__(self):
        super().__init__()
        self.MessageList = []
        self.Signalname = []
        self.Mesagename = []
        self.df = None
        self.excelpath=None

        self.__win()
        self.tk_button_SelcetFile = self.__tk_button_SelcetFile(self)
        self.tk_label_path = self.__tk_label_path(self)
        self.tk_frame_Excel = self.__tk_frame_Excel(self)
        self.tk_table_excel1 = self.__tk_table_excel1(self.tk_frame_Excel)
        self.tk_select_box_SheetName = self.__tk_select_box_SheetName(self.tk_frame_Excel)
        self.tk_label_Label31 = self.__tk_label_Label31(self.tk_frame_Excel)
        self.tk_select_box_SignalName = self.__tk_select_box_SignalName(self.tk_frame_Excel)
        self.tk_label_lable2 = self.__tk_label_lable2(self.tk_frame_Excel)
        self.tk_select_box_MessageID = self.__tk_select_box_MessageID(self.tk_frame_Excel)
        self.tk_label_lable3 = self.__tk_label_lable3(self.tk_frame_Excel)
        self.tk_select_box_CycleTime = self.__tk_select_box_CycleTime(self.tk_frame_Excel)
        self.tk_label_lablel5 = self.__tk_label_lablel5(self.tk_frame_Excel)
        self.tk_select_box_SignalDescription = self.__tk_select_box_SignalDescription(self.tk_frame_Excel)
        self.tk_label_label6 = self.__tk_label_label6(self.tk_frame_Excel)
        self.tk_select_box_StartByte = self.__tk_select_box_StartByte(self.tk_frame_Excel)
        self.tk_label_label7 = self.__tk_label_label7(self.tk_frame_Excel)
        self.tk_select_box_StartBit = self.__tk_select_box_StartBit(self.tk_frame_Excel)
        self.tk_label_label8 = self.__tk_label_label8(self.tk_frame_Excel)
        self.tk_select_box_BitLength = self.__tk_select_box_BitLength(self.tk_frame_Excel)
        self.tk_label_label9 = self.__tk_label_label9(self.tk_frame_Excel)
        self.tk_select_box_Factor = self.__tk_select_box_Factor(self.tk_frame_Excel)
        self.tk_label_label10 = self.__tk_label_label10(self.tk_frame_Excel)
        self.tk_select_box_Offset = self.__tk_select_box_Offset(self.tk_frame_Excel)
        self.tk_label_Label11 = self.__tk_label_Label11(self.tk_frame_Excel)
        self.tk_select_box_MinValue = self.__tk_select_box_MinValue(self.tk_frame_Excel)
        self.tk_label_Label12 = self.__tk_label_Label12(self.tk_frame_Excel)
        self.tk_select_box_MaxValue = self.__tk_select_box_MaxValue(self.tk_frame_Excel)
        self.tk_label_Label13 = self.__tk_label_Label13(self.tk_frame_Excel)
        self.tk_select_box_Unit = self.__tk_select_box_Unit(self.tk_frame_Excel)
        self.tk_label_lable1 = self.__tk_label_lable1(self)
        self.tk_label_Label21 = self.__tk_label_Label21(self)
        self.tk_select_box_SheetName = self.__tk_select_box_SheetName(self)
        self.tk_button_Start = self.__tk_button_Start(self)
        self.tk_label_err = self.__tk_label_err(self)
        self.tk_select_box_MessageName = self.__tk_select_box_MessageName(self.tk_frame_Excel)
        self.tk_button_ReadExcel = self.__tk_button_ReadExcel(self)
        self.tk_text_StauesText = self.__tk_text_StauesText(self)
        self.tk_label_label32 = self.__tk_label_label32(self)

        self.Can_MessageName=[]
        self.Can_MessageID=[]
        self.Can_MessageCycleTime=[]
        self.Can_MessageLength=[]
        self.Can_SignalName=[]
        self.Can_SignalDescription=[]
        self.Can_StartByte=[]
        self.Can_StartBit=[]
        self.Can_SignalBitLength=[]
        self.Can_SignalOffset=[]
        self.Can_Signalfactor=[]
        self.Can_SignalMinValue=[]
        self.Can_SignalMaxValue=[]
        self.Can_SignalUnit=[]




    def __win(self):
        self.title("ExcelToDBC_Ver1.0")
        # 设置窗口大小、居中
        width = 1000
        height = 700
        screenwidth = self.winfo_screenwidth()
        screenheight = self.winfo_screenheight()
        geometry = '%dx%d+%d+%d' % (width, height, (screenwidth - width) / 2, (screenheight - height) / 2)
        self.geometry(geometry)

        self.resizable(width=False, height=False)

    def scrollbar_autohide(self, vbar, hbar, widget):
        """自动隐藏滚动条"""

        def show():
            if vbar: vbar.lift(widget)
            if hbar: hbar.lift(widget)

        def hide():
            if vbar: vbar.lower(widget)
            if hbar: hbar.lower(widget)

        hide()
        widget.bind("<Enter>", lambda e: show())
        if vbar: vbar.bind("<Enter>", lambda e: show())
        if vbar: vbar.bind("<Leave>", lambda e: hide())
        if hbar: hbar.bind("<Enter>", lambda e: show())
        if hbar: hbar.bind("<Leave>", lambda e: hide())
        widget.bind("<Leave>", lambda e: hide())

    def v_scrollbar(self, vbar, widget, x, y, w, h, pw, ph):
        widget.configure(yscrollcommand=vbar.set)
        vbar.config(command=widget.yview)
        vbar.place(relx=(w + x) / pw, rely=y / ph, relheight=h / ph, anchor='ne')

    def h_scrollbar(self, hbar, widget, x, y, w, h, pw, ph):
        widget.configure(xscrollcommand=hbar.set)
        hbar.config(command=widget.xview)
        hbar.place(relx=x / pw, rely=(y + h) / ph, relwidth=w / pw, anchor='sw')

    def create_bar(self, master, widget, is_vbar, is_hbar, x, y, w, h, pw, ph):
        vbar, hbar = None, None
        if is_vbar:
            vbar = Scrollbar(master)
            self.v_scrollbar(vbar, widget, x, y, w, h, pw, ph)
        if is_hbar:
            hbar = Scrollbar(master, orient="horizontal")
            self.h_scrollbar(hbar, widget, x, y, w, h, pw, ph)
        self.scrollbar_autohide(vbar, hbar, widget)

    def __tk_button_SelcetFile(self, parent):
        btn = Button(parent, text="选择文件", takefocus=False,command=self.select_file )
        btn.place(x=15, y=10, width=90, height=34)
        return btn

    def __tk_select_box_MessageName(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=136, y=203, width=150, height=30)
        return cb


    def __tk_label_path(self, parent):
        label = Label(parent, text="未选择文件", anchor="center", )
        label.place(x=118, y=12, width=871, height=31)
        return label

    def __tk_frame_Excel(self, parent):
        frame = Frame(parent, )
        frame.place(x=17, y=53, width=976, height=496)
        return frame

    def __tk_table_excel1(self, parent):
        # 表头字段 表头宽度
        #columns = {"ID": 112, "字段#1": 169, "字段#2": 282}
        #tk_table = Treeview(parent, show="headings", columns=list(columns), )
        tk_table = Treeview(parent, show="headings", )
        # for text, width in columns.items():  # 批量设置列属性
        #     tk_table.heading(text, text=text, anchor='center')
        #     tk_table.column(text, anchor='center', width=width, stretch=False)  # stretch 不自动拉伸

        tk_table.place(x=0, y=5, width=976, height=185)
        return tk_table

    def __tk_select_box_SheetName(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=136, y=203, width=150, height=30)
        return cb

    def __tk_label_Label31(self, parent):
        label = Label(parent, text="SignalName:", anchor="center", )
        label.place(x=12, y=325, width=107, height=30)
        return label

    def __tk_select_box_SignalName(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=136, y=326, width=150, height=30)
        return cb

    def __tk_label_lable2(self, parent):
        label = Label(parent, text="MessageID", anchor="center", )
        label.place(x=9, y=244, width=110, height=30)
        return label

    def __tk_select_box_MessageID(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=137, y=244, width=150, height=30)
        return cb

    def __tk_label_lable3(self, parent):
        label = Label(parent, text="CycleTime", anchor="center", )
        label.place(x=10, y=286, width=109, height=30)
        return label

    def __tk_select_box_CycleTime(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=135, y=287, width=150, height=30)
        return cb

    def __tk_label_lablel5(self, parent):
        label = Label(parent, text="SignalDescription", anchor="center", )
        label.place(x=14, y=368, width=103, height=30)
        return label

    def __tk_select_box_SignalDescription(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=136, y=368, width=150, height=30)
        return cb

    def __tk_label_label6(self, parent):
        label = Label(parent, text="StartByte", anchor="center", )
        label.place(x=292, y=202, width=110, height=30)
        return label

    def __tk_select_box_StartByte(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=408, y=203, width=150, height=30)
        return cb

    def __tk_label_label7(self, parent):
        label = Label(parent, text="StartBit", anchor="center", )
        label.place(x=292, y=241, width=110, height=30)
        return label

    def __tk_select_box_StartBit(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=408, y=241, width=150, height=30)
        return cb

    def __tk_label_label8(self, parent):
        label = Label(parent, text="BitLength", anchor="center", )
        label.place(x=293, y=284, width=110, height=30)
        return label

    def __tk_select_box_BitLength(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=408, y=283, width=150, height=30)
        return cb

    def __tk_label_label9(self, parent):
        label = Label(parent, text="Factor", anchor="center", )
        label.place(x=292, y=324, width=110, height=30)
        return label

    def __tk_select_box_Factor(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=410, y=323, width=150, height=30)
        return cb

    def __tk_label_label10(self, parent):
        label = Label(parent, text="Offset", anchor="center", )
        label.place(x=292, y=367, width=110, height=30)
        return label

    def __tk_select_box_Offset(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=409, y=368, width=150, height=30)
        return cb

    def __tk_label_Label11(self, parent):
        label = Label(parent, text="MinValue", anchor="center", )
        label.place(x=14, y=410, width=110, height=30)
        return label

    def __tk_select_box_MinValue(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=136, y=410, width=150, height=30)
        return cb

    def __tk_label_Label12(self, parent):
        label = Label(parent, text="MaxValue", anchor="center", )
        label.place(x=292, y=410, width=110, height=30)
        return label

    def __tk_select_box_MaxValue(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=410, y=411, width=150, height=30)
        return cb

    def __tk_label_Label13(self, parent):
        label = Label(parent, text="Unit", anchor="center", )
        label.place(x=15, y=454, width=110, height=30)
        return label

    def __tk_select_box_Unit(self, parent):
        cb = Combobox(parent, state="readonly", )
        cb['values'] = ("列表框", "Python", "Tkinter Helper")
        cb.place(x=134, y=453, width=150, height=30)
        return cb

    def __tk_label_lable1(self, parent):
        label = Label(parent, text="MeassageName:", anchor="center", )
        label.place(x=25, y=253, width=110, height=30)
        return label

    def __tk_label_Label21(self, parent):
        label = Label(parent, text="SheetName", anchor="center", )
        label.place(x=21, y=560, width=120, height=50)
        return label

    def __tk_select_box_SheetName(self, parent):
        cb = Combobox(parent, state="readonly", )

        cb.place(x=153, y=560, width=147, height=52)
        return cb

    def __tk_button_Start(self, parent):
        btn = Button(parent, text="开始转换", takefocus=False,command=self.ExcelToDBC )
        btn.place(x=462, y=559, width=117, height=48)
        return btn

    def __tk_label_err(self,parent):
        label = Label(parent,text=" ",anchor="center", )
        label.place(x=18, y=624, width=566, height=68)
        return label

    def __tk_button_ReadExcel(self,parent):
        btn = Button(parent, text="读取Excel", takefocus=False,command=self.read_excel)
        btn.place(x=327, y=561, width=118, height=48)
        return btn


    def __tk_text_StauesText(self,parent):
        text = Text(parent)
        text.place(x=602, y=299, width=383, height=362)
        self.create_bar(parent, text,True, False, 602, 299, 383,362,1000,700)
        return text
    def __tk_label_label32(self,parent):
        label = Label(parent,text="状态提示",anchor="center", )
        label.place(x=752, y=259, width=87, height=30)
        return label



    def select_file(self):
        file_path = filedialog.askopenfilename(
            filetypes=[("Excel文件", "*.xlsx *.xls"), ("所有文件", "*.*")]
        )

        if not file_path:
            return

        self.excelpath=file_Path
        self.tk_label_path.config(text=f"已选择: {file_path}")
        rows = self.read_with_pandas(file_path)
        if rows:
            # self.display_data(rows)
            self.tk_text_StauesText.insert("end",f"成功读取Excel文件\n")
        else:
            self.tk_text_StauesText.insert("end","文件打开失败，请重新选择\n")



    def read_with_pandas(self,file_path):
        try:
            with pd.ExcelFile(file_path) as excel:
                self.sheet_names = excel.sheet_names  # 获取所有sheet名称
                self.tk_select_box_SheetName.config(values=self.sheet_names)
                return True
        except Exception as e:
            self.tk_text_StauesText.insert("end", f"pandas读取错误:{str(e)}\n")
            return False



    def read_excel(self):
        try:
            with pd.ExcelFile(self.excelpath) as excel:
                self.df_header = pd.read_excel(excel, sheet_name=self.tk_select_box_SheetName.get(),nrows=0)
                #列标题
                self.column_headers = self.df_header.columns.tolist()
                self.df = pd.read_excel(self.excelpath, self.tk_select_box_SheetName.get())


                self.tk_select_box_MessageName.config(values=self.column_headers)
                self.tk_select_box_MessageID.config(values=self.column_headers)
                self.tk_select_box_CycleTime.config(values=self.column_headers)
                self.tk_select_box_SignalName.config(values=self.column_headers)
                self.tk_select_box_StartByte.config(values=self.column_headers)
                self.tk_select_box_StartBit.config(values=self.column_headers)
                self.tk_select_box_Factor.config(values=self.column_headers)
                self.tk_select_box_Offset.config(values=self.column_headers)
                self.tk_select_box_MinValue.config(values=self.column_headers)
                self.tk_select_box_MaxValue.config(values=self.column_headers)
                self.tk_select_box_BitLength.config(values=self.column_headers)
                self.tk_select_box_Unit.config(values=self.column_headers)
                self.tk_select_box_SignalDescription.config(values=self.column_headers)

                rows=self.df.to_dict('records')
                if rows:
                    self.display_data(rows)
                    self.tk_text_StauesText.insert("end", f"成功读取 {len(rows)} 行数据\n")
                else:
                    self.tk_text_StauesText.insert("end", f"读取失败或文件为空\n")
        except Exception as e:
            self.tk_text_StauesText.insert("end", f"工作表打开失败，请检查SheetName是否已选择\n")


    def display_data(self, rows):
        # 清除现有表格
        for item in self.tk_table_excel1.get_children():
            self.tk_table_excel1.delete(item)

        # 设置列
        if not rows:
            return

        columns = list(rows[0].keys())
        self.tk_table_excel1["columns"] = columns
        self.tk_table_excel1["show"] = "headings"

        # 设置列标题
        for col in columns:
            self.tk_table_excel1.heading(col, text=col)
            self.tk_table_excel1.column(col, width=100)

        # 添加数据
        for row in rows:
            values = [str(row.get(col, "")) for col in columns]
            self.tk_table_excel1.insert("", tk.END, values=values)

    def ExcelToDBC(self):
        try:
            self.Can_MessageName=self.df[self.tk_select_box_MessageName.get()]
            self.Can_MessageID=self.df[self.tk_select_box_MessageID.get()]
            self.Can_MessageCycleTime=self.df[self.tk_select_box_CycleTime.get()]
            self.Can_SignalName=self.df[self.tk_select_box_SignalName.get()]
            self.Can_SignalDescription=self.df[self.tk_select_box_SignalDescription.get()]
            self.Can_StartByte=self.df[self.tk_select_box_StartByte.get()]
            self.Can_StartBit=self.df[self.tk_select_box_StartBit.get()]
            self.Can_SignalBitLength=self.df[self.tk_select_box_BitLength.get()]
            self.Can_SignalOffset=self.df[self.tk_select_box_Offset.get()]
            self.Can_Signalfactor=self.df[self.tk_select_box_Factor.get()]
            self.Can_SignalMinValue=self.df[self.tk_select_box_MinValue.get()]
            self.Can_SignalMaxValue=self.df[self.tk_select_box_MaxValue.get()]
            self.Can_SignalUnit=self.df[self.tk_select_box_Unit.get()]

            self.old_Can_MessageName = self.Can_MessageName
            self.old_Can_SignalName = self.Can_SignalName
            for name in self.old_Can_MessageName:
                if (name != "nan"):
                    name = str(name).replace(" ", "_")
                    self.Mesagename.append(name)
            for name in self.old_Can_SignalName:
                if (name != "nan"):
                    name = str(name).replace(" ", "_")
                    self.Signalname.append(name)

            self.Can_SignalName = self.Signalname
            self.Can_MessageName = self.Mesagename
            self.Can_MessageID = [str(x) if not (pd.isna(x) or x is None) else "000" for x in self.Can_MessageID]
            self.Can_SignalMinValue = [str(x) if not (pd.isna(x) or x is None) else "0" for x in self.Can_SignalMinValue]
            self.Can_SignalMaxValue = [str(x) if not (pd.isna(x) or x is None) else "255" for x in self.Can_SignalMaxValue]
            for num in range(len(self.Can_MessageID)):
                if (self.Can_MessageID[num] != "000"):
                    self.Can_SignalName[num] = "nan"
            try:
                self.messagelist=self.CreateMessageLIST()
                node1 = Node(
                    name='VCU',
                    comment="test"
                )
                database = Database(
                    messages=self.messagelist,
                    nodes=[node1],
                    version='1.0'
                )
                cantools.database.dump_file(database, file_Path.split('.')[0]+self.tk_select_box_SheetName.get()+".dbc")

                self.tk_text_StauesText.insert("end", f"文件转换完成，文件转换存储地址为：{file_Path.split('.')[0]+self.tk_select_box_SheetName.get()}.dbc\n")
            except Exception as e:
                self.tk_text_StauesText.insert("end", f"文件转换失败,请检查EXCEL文件\n")
        except Exception as e:
            self.tk_text_StauesText.insert("end", f"转换失败，请选择消息名称、信号名称等\n")


    def CreateSignalLIST(self):
        self.signalList = []  # 信号列表
        self.SignalLIST = []
        for num in range(1, len(self.Can_SignalName)):
            if (self.Can_SignalName[num] == "nan"):
                self.SignalLIST.append(self.signalList)
                self.signalList = []
            else:
                siganl1 = Signal(
                    name=self.Can_SignalName[num],
                    start=int(self.Can_StartBit[num]),
                    length=int(self.Can_SignalBitLength[num]),
                    byte_order="little_endian",
                    minimum=float(self.Can_SignalMinValue[num]),
                    maximum=float(self.Can_SignalMaxValue[num]),
                    comment=str(self.Can_SignalDescription[num]),
                    # raw_initial=0.1,#初始值
                    is_signed=False,
                )
                siganl1.offset = float(self.Can_SignalOffset[num])
                siganl1.scale = float(self.Can_Signalfactor[num])
                self.signalList.append(siganl1)
        self.SignalLIST.append(self.signalList)
        print(self.signalList)
        return self.SignalLIST

    def CreateMessageLIST(self):
        self.SignalList = self.CreateSignalLIST()
        flag = 0
        for num in range(0, len(self.Can_MessageID)):
            if (self.Can_MessageID[num] != "000"):
                message1 = Message(
                    frame_id=int(self.Can_MessageID[num], 16),
                    name=self.Can_MessageName[num],
                    length=8,
                    signals=self.SignalList[flag],
                    bus_name="PFC",
                    header_byte_order='little_endian',
                    is_extended_frame=True,
                    send_type='cyclic',
                    senders=['PFC'],
                    cycle_time=int(self.Can_MessageCycleTime[num]),
                    comment='input voltage',
                    unused_bit_pattern=255,
                )
                self.MessageList.append(message1)
                flag = flag + 1
        return self.MessageList


class Win(WinGUI):
    def __init__(self, controller):
        self.ctl = controller
        super().__init__()
        self.__event_bind()
        self.__style_config()
        self.ctl.init(self)

    def __event_bind(self):
        pass

    def __style_config(self):
        pass


if __name__ == "__main__":
    win = WinGUI()
    win.mainloop()